﻿CREATE PROCEDURE [dbo].[s_Upload_Process]
	@UploadID int,
	@PurchaseBatchID int
AS

DECLARE @ContractHolderID int,@CompanyID int
SELECT @ContractHolderID = ContractHolderID,@CompanyID = CompanyID
FROM t_UploadInfo UI INNER JOIN t_Upload U ON U.UploadInfoID = UI.ID WHERE U.ID = @UploadID

IF NOT EXISTS(SELECT * FROM t_PurchaseBatch WHERE ID = @PurchaseBatchID AND ContractHolderID = @ContractHolderID)BEGIN
	RAISERROR('Contract Holder must be the same in Upload and Purchase batches.',16,1)
	RETURN
END
IF NOT EXISTS(SELECT * FROM t_PurchaseBatch WHERE ID = @PurchaseBatchID AND CompanyID = @CompanyID)BEGIN
	RAISERROR('Company must be the same in Upload and Purchase batches.',16,1)
	RETURN
END


DECLARE @Context int EXEC @Context = sp__Context_ArchiveOff 1

DECLARE @LoanID int,@StatusID int SET @StatusID = dbo.fs_StateID__Batches__NewInBatch()
EXEC @LoanID = sec_AddNewLoan @CompanyID,@StatusID

UPDATE t_Loan SET ContractHolderID = (
SELECT ContractHolderID FROM t_UploadInfo UI INNER JOIN t_Upload U ON U.UploadInfoID = UI.ID WHERE U.ID = @UploadID
)
WHERE ID = @LoanID
SELECT * FROM t_Loan WHERE ID = @LoanID

INSERT INTO t_PurchaseBatch_Loans(BatchID,LoanID)VALUES(@PurchaseBatchID,@LoanID)
IF @@ERROR <> 0 RETURN

SELECT     @LoanID AS LoanID,L_B.BorrowerID,
B.FirstName,
B.LastName,
B.SSN,
ISNULL(U.[Last Name], dbo.clr_RegEx_MatchGroup(U.[Full Name], N'(^[^,]+)', 1)) AS [Last Name], ISNULL(U.[First Name], 
dbo.clr_RegEx_MatchGroup(U.[Full Name], N'^[^,]+,\s?(.+)', 1)) AS [First Name], U.[Full Name], U.[Primary SSN], U.Address, U.City, U.St, U.[Zip Code], 
U.[Home Phone #], U.[Work Phone #], U.[Joint Name], U.[Joint Address], U.[Joint City], U.[Joint State], U.[Joint Zipcode], U.[Joint SSN], 
U.[Joint H Phone#], U.[Joint W Phone#], U.[Primary Cell #]
INTO #Info
FROM         t_Upload AS U
INNER JOIN t_Loan_Borrower AS L_B ON L_B.LoanID = @LoanID
INNER JOIN t_Borrower AS B ON B.ID = L_B.BorrowerID
WHERE     (U.ID = @UploadID)

UPDATE B SET SSN = i.[Primary SSN],FirstName = i.[First Name],LastName = i.[Last Name],PhoneHome = i.[Home Phone #]
FROM t_Borrower B INNER JOIN #Info i ON i.BorrowerID = B.ID

UPDATE A SET Street = i.Address,CityName = i.City,StateCode = i.St,Zip = i.[Zip Code]
FROM t_Address A INNER JOIN t_Borrower B ON A.BorrowerID = B.ID INNER JOIN #Info i ON i.BorrowerID = B.ID WHERE A.Pos = 0

UPDATE E SET Phone = i.[Work Phone #]
FROM t_Employment E INNER JOIN t_Borrower B ON E.BorrowerID = B.ID INNER JOIN #Info i ON i.BorrowerID = B.ID WHERE E.Pos = 0

SELECT * FROM #Info
SELECT     B.ID, B.FirstName, B.LastName, B.SSN, B.PhoneHome, B.PhoneCell, B.FullName,
A.Street, A.CityName, A.Zip, A.StateCode, E.Phone
FROM         t_Borrower AS B INNER JOIN
                      t_Address AS A ON A.BorrowerID = B.ID INNER JOIN
                      t_Loan_Borrower AS L_B ON B.ID = L_B.BorrowerID INNER JOIN
                      t_Employment E ON B.ID = E.BorrowerID
WHERE     (L_B.LoanID = @LoanID) AND E.Pos = 0 AND A.Pos = 0

SELECT     @LoanID AS LoanID, U.[Car Year], U.Make, U.Model, U.Serial#, U.Mileage, U.[Black Book Value (Avg)]
INTO [#CarInfo]FROM t_Upload AS U INNER JOIN t_Loan_Borrower AS L_B ON L_B.LoanID = @LoanID
WHERE     (U.ID = @UploadID)

IF NOT EXISTS(SELECT * FROM t_Car WHERE VIN = (SELECT Serial# FROM #CarInfo))
	INSERT INTO t_Car(VIN)SELECT Serial# FROM #CarInfo

UPDATE CI SET VIN = i.Serial#
FROM t_CarInfo CI INNER JOIN #CarInfo i ON i.LoanID = CI.LoanID

UPDATE t_Upload SET LoanID = @LoanID WHERE ID = @UploadID

RETURN @LoanID
